﻿namespace AnyOffice.DAL
{
    using AnyOffice.Model;
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;

    public class FlowFormTypeDao
    {
        public bool Delete(SqlConnection conn, int formId)
        {
            string commandText = "DECLARE @Ret bit IF EXISTS ( SELECT [Flow_Type].[FormId] FROM [Flow_Type] WHERE [Flow_Type].[FormId] = @FormId ) SET @Ret = 0 ELSE BEGIN DELETE FROM [dbo].[Flow_Form_Type] WHERE [FormId] = @FormId SET @Ret = 1 END SELECT @Ret AS CanDelete";
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@FormId", SqlDbType.Int) };
            commandParameters[0].Value = formId;
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, commandText, commandParameters);
            return ((obj2 != null) && Convert.ToBoolean(obj2));
        }

        public int Insert(SqlConnection conn, FlowFormTypeData data)
        {
            string commandText = "INSERT INTO [dbo].[Flow_Form_Type] ( [FormName], [FormModel] ) VALUES ( @FormName, @FormModel ) SELECT SCOPE_IDENTITY() AS RetValue";
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@FormName", SqlDbType.NVarChar, 200);
            commandParameters[0].Value = data.FormName;
            commandParameters[1] = new SqlParameter("@FormModel", SqlDbType.NText);
            if (data.FormModelIsNull)
            {
                commandParameters[1].Value = DBNull.Value;
            }
            else
            {
                commandParameters[1].Value = data.FormModel;
            }
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, commandText, commandParameters);
            if (obj2 != null)
            {
                return Convert.ToInt32(obj2);
            }
            return -1;
        }

        public DataTable SelectAll(SqlConnection conn)
        {
            string commandText = "DECLARE @FormTable table( FormId int, FormName nvarchar(200), CanDelete bit ) INSERT INTO @FormTable SELECT [FormId], [FormName], 0 FROM [dbo].[Flow_Form_Type] WHERE [FormId] IN(SELECT DISTINCT [FormId] FROM [Flow_Type]) ORDER BY [FormId] ASC INSERT INTO @FormTable SELECT [FormId], [FormName], 1 FROM [dbo].[Flow_Form_Type] WHERE [FormId] NOT IN(SELECT DISTINCT [FormId] FROM [Flow_Type]) ORDER BY [FormId] ASC SELECT * FROM @FormTable ORDER BY FormId ASC";
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText);
            set.Tables[0].TableName = "Flow_Form_Type";
            return set.Tables[0];
        }

        public DataTable SelectByFlowId(SqlConnection conn, int flowId)
        {
            string commandText = "SELECT [Flow_Form_Type].[FormId], [Flow_Form_Type].[FormName], [Flow_Form_Type].[FormModel] FROM [dbo].[Flow_Form_Type], [dbo].[Flow_Type] WHERE [Flow_Form_Type].[FormId] = [Flow_Type].[FormId] AND [Flow_Type].[FlowId] = @FlowId";
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@FlowId", SqlDbType.Int) };
            commandParameters[0].Value = flowId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText, commandParameters);
            set.Tables[0].TableName = "Flow_Form_Type";
            return set.Tables[0];
        }

        public DataTable SelectByFormId(SqlConnection conn, int formId)
        {
            string commandText = "DECLARE @CanDelete bit IF EXISTS( SELECT [Flow_Type].[FormId] FROM [Flow_Type] WHERE [Flow_Type].[FormId] = @FormId ) SET @CanDelete = 0 ELSE SET @CanDelete = 1 SELECT [FormId], [FormName], [FormModel], @CanDelete AS CanDelete FROM [dbo].[Flow_Form_Type] WHERE [FormId] = @FormId";
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@FormId", SqlDbType.Int) };
            commandParameters[0].Value = formId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText, commandParameters);
            set.Tables[0].TableName = "Flow_Form_Type";
            return set.Tables[0];
        }

        public DataTable SelectByRunId(SqlConnection conn, int runId)
        {
            string commandText = "SELECT [Flow_Form_Type].[FormId], [Flow_Form_Type].[FormName], [Flow_Form_Type].[FormModel] FROM [dbo].[Flow_Form_Type], [dbo].[Flow_Run], [dbo].[Flow_Type] WHERE [Flow_Form_Type].[FormId] = [Flow_Type].[FormId] AND [Flow_Type].[FlowId] = [Flow_Run].[FlowId] AND [Flow_Run].[RunId] = @RunId";
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@RunId", SqlDbType.Int) };
            commandParameters[0].Value = runId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText, commandParameters);
            set.Tables[0].TableName = "Flow_Form_Type";
            return set.Tables[0];
        }

        public bool UpdateModel(SqlConnection conn, FlowFormTypeData data)
        {
            string commandText = "UPDATE [dbo].[Flow_Form_Type] SET [FormModel] = @FormModel WHERE [FormId] = @FormId";
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@FormId", SqlDbType.Int);
            commandParameters[0].Value = data.FormId;
            commandParameters[1] = new SqlParameter("@FormModel", SqlDbType.NText);
            if (data.FormModelIsNull)
            {
                commandParameters[1].Value = DBNull.Value;
            }
            else
            {
                commandParameters[1].Value = data.FormModel;
            }
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, commandText, commandParameters);
            return true;
        }

        public bool UpdateName(SqlConnection conn, FlowFormTypeData data)
        {
            string commandText = "UPDATE [dbo].[Flow_Form_Type] SET [FormName] = @FormName WHERE [FormId] = @FormId";
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@FormId", SqlDbType.Int);
            commandParameters[0].Value = data.FormId;
            commandParameters[1] = new SqlParameter("@FormName", SqlDbType.NVarChar, 200);
            commandParameters[1].Value = data.FormName;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, commandText, commandParameters);
            return true;
        }
    }
}

